/*
 * PizzaManager.java
 *
 * Created on 30 octobre 2007, 16:46
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package com.ifips5.nal.epizza.web;

import com.ifips5.nal.epizza.util.JDBCManager;
import com.ifips5.nal.epizza.web.model.Ingredient;
import com.ifips5.nal.epizza.web.model.Pizza;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import javax.sql.RowSet;
import javax.sql.rowset.WebRowSet;
import toPDF.CreationPDF;



/**
 *
 * @author Moi
 */
public class PizzaManager
{
    private BeanManager beanManager = null;
    private Map<Integer, Pizza> pizzaMap = new HashMap();
    private List<Pizza> pizzaList = new LinkedList();
    private Pizza currentPizza = null;


    public PizzaManager()
    {
    }
    
    /** Creates a new instance of PizzaManager */
    public PizzaManager(BeanManager beanManager)
    {
        this.beanManager = beanManager;
    }
    
    public void loadPizza(String idPizza)
    {
        beanManager.getIngredientManager().loadIngredients();
        String command =    "SELECT * FROM pizza, ingredient_has_pizza " +
                            "WHERE pizza.idPizza=\""+idPizza+"\"" +
                            "AND pizza.idPizza = ingredient_has_pizza.Pizza_idPizza";

        WebRowSet wrs = JDBCManager.getInstance().getRowSet(command, null);
        try
        {
            while(wrs.next())
            {
                if(this.currentPizza == null)
                {
                    this.currentPizza = new Pizza(wrs.getInt(1), wrs.getString(2), wrs.getBoolean(3), wrs.getBoolean(4), wrs.getDouble(5));
                }
                this.currentPizza.addIngredient(beanManager.getIngredientManager().getIngredientMap().get(wrs.getInt("Ingredient_idIngredient")));
            }
            wrs.close();
        } catch (SQLException ex)
        {
            ex.printStackTrace();
        }
    }
    
    
    public void deletePizza(String idPizza)
    {
        try
        {
            Connection connection = JDBCManager.getInstance().getConnection();
            Statement stmt = connection.createStatement();
            stmt.addBatch("DELETE FROM menu_has_pizza WHERE Pizza_idPizza="+idPizza);
            stmt.addBatch("DELETE FROM pizza_has_commande WHERE Pizza_idPizza="+idPizza);
            stmt.addBatch("DELETE FROM ingredient_has_pizza WHERE Pizza_idPizza="+idPizza);            
            stmt.addBatch("DELETE FROM pizza WHERE idPizza=\""+idPizza+"\"");
            stmt.executeBatch();   
            connection.commit();
            connection.close();    
        } catch (SQLException ex)
        {
            ex.printStackTrace();
        }
        beanManager.updatePDF();
    }
    
    
    public void deletePizza(String[] delPizzas)
    {
        try
        {
            Connection connection = JDBCManager.getInstance().getConnection();
            Statement stmt = connection.createStatement();
            
            for(int i = 0; i < delPizzas.length ; i++)
            {
                stmt.addBatch("DELETE FROM menu_has_pizza WHERE Pizza_idPizza="+delPizzas[i]);
                stmt.addBatch("DELETE FROM pizza_has_commande WHERE Pizza_idPizza="+delPizzas[i]);
                stmt.addBatch("DELETE FROM ingredient_has_pizza WHERE Pizza_idPizza="+delPizzas[i]);
                                
                                
                stmt.addBatch("DELETE FROM pizza WHERE idPizza=\""+delPizzas[i]+"\"");

            }
            
            stmt.executeBatch();   
            connection.commit();
            connection.close();    
        } catch (SQLException ex)
        {
            ex.printStackTrace();
        }
        beanManager.updatePDF();
    }
    
    public void insertPizza(String name, String prix, String vege, String perso, String[] ingredients)
    {
        boolean bVegetarienne = Boolean.getBoolean(vege);
        boolean bPerso = Boolean.getBoolean(perso);
        double dPrix;
        int idInsertPizza = -1;
        try
        {
            dPrix = Double.parseDouble(prix);
        } catch (NumberFormatException ex)
        {
            dPrix = 8.0;
        }
        
        try
        {
            Connection connection = JDBCManager.getInstance().getConnection();
            Statement stmt = connection.createStatement();
            //stmt.addBatch("INSERT INTO pizza(name, vegetarienne, perso, prix) VALUES ( \""+name+"\", "+bVegetarienne+", "+bPerso+", "+dPrix+")");
            // Insertion des ingredients
            String command = "INSERT INTO pizza(name, vegetarienne, perso, prix) VALUES ( \""+name+"\", "+bVegetarienne+", "+bPerso+", "+dPrix+")";
            stmt.executeUpdate(command, stmt.RETURN_GENERATED_KEYS);
            
            ResultSet cle = stmt.getGeneratedKeys();
            if(cle.next())
            {
                idInsertPizza = cle.getInt(1);
            }
            
            stmt = connection.createStatement();
            
            for(int i = 0; i < ingredients.length ; i++)
            {
                stmt.addBatch("INSERT INTO ingredient_has_pizza(Ingredient_idIngredient, Pizza_idPizza) VALUES ( "+ingredients[i]+", "+idInsertPizza+")");     
            }
            stmt.executeBatch();
            
            
            
            connection.commit();
            connection.close();    
        } catch (SQLException ex)
        {
            ex.printStackTrace();
        }
        
        beanManager.updatePDF();
    }    
    
    
    public void loadPizzas()
    {
        String command = "SELECT * FROM pizza";
        int[] keyCol = {1};
        WebRowSet wrs =  JDBCManager.getInstance().getRowSet(command, keyCol);
        try
        {
            while(wrs.next())
            {
                Pizza currentPizza = new Pizza(wrs.getInt(1), wrs.getString(2), wrs.getBoolean(3), wrs.getBoolean(4), wrs.getDouble(5));
                this.pizzaMap.put(wrs.getInt(1), currentPizza);
                this.pizzaList.add(currentPizza);
            }
        } catch (SQLException ex)
        {
            ex.printStackTrace();
        }                
        
    }
    
    
    public void getAllPizzas()
    {
        // Chargement des ingredients
        beanManager.getIngredientManager().loadIngredients();
        
        
        String command =    "SELECT * FROM pizza, ingredient_has_pizza " +
                            "WHERE pizza.idPizza = ingredient_has_pizza.Pizza_idPizza " +
                            "ORDER BY pizza.idPizza";
        
        WebRowSet wrs = JDBCManager.getInstance().getRowSet(command, null);
        try
        {
            int currentIdPizza = -1;
            Pizza currentPizza = null;
         
            
            while(wrs.next())
            {
                if(wrs.getInt("idPizza") != currentIdPizza)
                {
                    currentPizza = new Pizza(wrs.getInt(1), wrs.getString(2), wrs.getBoolean(3), wrs.getBoolean(4), wrs.getDouble(5));
                    // On ajoute la pizza a la liste
                    this.pizzaList.add(currentPizza);
                    this.pizzaMap.put(wrs.getInt(1), currentPizza);
                    currentIdPizza = wrs.getInt(1);
                }
                //System.out.println(wrs.getInt("Ingredient_idIngredient"));
                currentPizza.addIngredient(beanManager.getIngredientManager().getIngredientMap().get(wrs.getInt("Ingredient_idIngredient")));
            }
            wrs.close();
        } catch (SQLException ex)
        {
            System.out.println("PB SQL PIZZA");
            ex.printStackTrace();
        }
    }
    
    
    public void updatePizza(String id, String name, String prix, String vege, String perso, String[] ingredients)
    {
        // Faire un rowSet et faire des modifications avant le commit
        boolean bVegetarienne = Boolean.getBoolean(vege);
        boolean bPerso = Boolean.getBoolean(perso);
        double dPrix;
        try
        {
            dPrix = Double.parseDouble(prix);
        } catch (NumberFormatException ex)
        {
            dPrix = 8.0;
        }
        
        try
        {
            Connection connection = JDBCManager.getInstance().getConnection();
            Statement stmt = connection.createStatement();
            stmt.addBatch("DELETE FROM ingredient_has_pizza WHERE Pizza_idPizza="+id);
            stmt.addBatch("UPDATE pizza SET name = \""+name+"\", vegetarienne="+bVegetarienne+", perso="+bPerso+", prix="+dPrix+" WHERE idPizza="+id);
            for(int i = 0; i < ingredients.length ; i++)
            {
                stmt.addBatch("INSERT INTO ingredient_has_pizza(Ingredient_idIngredient, Pizza_idPizza) VALUES ( "+ingredients[i]+", "+id+")");     
            }
            stmt.executeBatch();
            connection.commit();
            connection.close();    
        } catch (SQLException ex)
        {
            ex.printStackTrace();
        }    
        beanManager.updatePDF();
    }
    

            
    public void printRowSet(RowSet rs)
    {
        try
        {
            ResultSetMetaData rsmd = rs.getMetaData();
            int nbColumn = rsmd.getColumnCount();
            for(int i = 1; i <= nbColumn; i++ )
            {
                System.out.print(rsmd.getColumnName(i) + "\t\t");
            }
            System.out.println("");
            
            while(rs.next())
            {
                System.out.print("-");
                for(int i = 1; i<= nbColumn; i++)
                {
                    System.out.print(rs.getObject(i)+"\t\t");
                }
                System.out.println("");
            }
        } catch (SQLException ex)
        {
            ex.printStackTrace();
        }
    }
    
    public void searchPizza(String pattern)
    {
        String[] tab = pattern.split("\\s");

        
    
        String command =    "SELECT * FROM pizza, ingredient_has_pizza " +
                            "WHERE pizza.idPizza = ingredient_has_pizza.Pizza_idPizza " +
                            "AND (idPizza IN " +
                            "( " +
                            "SELECT DISTINCT idPizza FROM pizza, ingredient_has_pizza, ingredient " +
                                "WHERE idPizza = Pizza_idPizza AND idIngredient = Ingredient_idIngredient " +
                                "AND (ingredient.name LIKE '%"+tab[0]+"%' ";
        
                            for (int i = 1; i < tab.length; i++) 
                            {
                                String currentPattern = tab[i];
                                command += "OR ingredient.name LIKE '%"+currentPattern+"%' ";
                            }
                            command += ")" +
                            " ) " +
                            "OR (pizza.name LIKE '%"+tab[0]+"%' ";
                            
                            for (int i = 1; i < tab.length; i++) 
                            {
                                String currentPattern = tab[i];
                                command += "OR pizza.name LIKE '%"+currentPattern+"%' ";
                            }                       
                            command += ")) " +
                            "ORDER BY pizza.idPizza";
            
       
//       command = "SELECT idPizza FROM pizza, ingredient_has_pizza, ingredient " +
//                "WHERE idPizza = Pizza_idPizza AND idIngredient = Ingredient_idIngredient " +
//               // "AND pizza.name LIKE '%"+pattern+"%' " +
//                "AND ingredient.name LIKE '%"+pattern+"%' ";
//       
        
        
//        String command =    "(SELECT * FROM pizza, ingredient_has_pizza " +
//                            "WHERE pizza.idPizza = ingredient_has_pizza.Pizza_idPizza " +
//                            "AND pizza.name LIKE '%"+tab[0]+"%' ";
//        
//        for (int i = 1; i < tab.length; i++) 
//        {
//            String currentPattern = tab[i];
//            command += "OR pizza.name LIKE '%"+currentPattern+"%' ";
//        }
//        
//        //command += " ORDER BY pizza.idPizza";
//        
//        
//        command += "UNION SELECT * FROM pizza, ingredient_has_pizza " +
//                "WHERE pizza.idPizza = ingredient_has_pizza.Pizza_idPizza " +
//                "AND Ingredient_idIngredient IN (SELECT * FROM ingredient " +
//                        "WHERE name = LIKE '%"+tab[0]+"%' ";
//        
//        for (int i = 1; i < tab.length; i++) 
//        {
//            String currentPattern = tab[i];
//            command += "OR pizza.name LIKE '%"+currentPattern+"%' ";
//        }
//        
//         command += ")) ORDER BY pizza.idPizza";
            
//        String command = "SELECT idPizza FROM pizza, ingredient_has_pizza, ingredient " +
//                "WHERE idPizza = Pizza_idPizza AND idIngredient = Ingredient_idIngredient " +
//               // "AND pizza.name LIKE '%"+pattern+"%' " +
//                "AND ingredient.name LIKE '%"+pattern+"%' ";
        
        int[] keyCol = {1};

        WebRowSet wrs =  JDBCManager.getInstance().getRowSet(command, keyCol);
        try
        {
            int currentIdPizza = -1;
            Pizza currentPizza = null;
            while(wrs.next())
            {
                if(wrs.getInt("idPizza") != currentIdPizza)
                {
                    currentPizza = new Pizza(wrs.getInt(1), wrs.getString(2), wrs.getBoolean(3), wrs.getBoolean(4), wrs.getDouble(5));
                    // On ajoute la pizza a la liste
                    this.pizzaList.add(currentPizza);
                    this.pizzaMap.put(wrs.getInt(1), currentPizza);
                    currentIdPizza = wrs.getInt(1);
                }
                currentPizza.addIngredient(beanManager.getIngredientManager().getIngredientMap().get(wrs.getInt("Ingredient_idIngredient")));
            }
        } catch (SQLException ex)
        {
            ex.printStackTrace();
        }
        
    
    }    
    

    public void searchPizzaFromIngredient(String pattern)
    {
        String[] tab = pattern.split("\\s");
        String command = "SELECT * FROM pizza WHERE idPizza IN " +
                "(SELECT * Pizza_idPizza From ingredient_has_pizza WHERE Ingredient_idIngredient IN " +
                "(SELECT idIngredient FROM ingredient WHERE name LIKE '%"+tab[0]+"%' ";
        
        for (int i = 1; i < tab.length; i++) 
        {
            String currentPattern = tab[i];
            command += "OR name LIKE '%"+currentPattern+"%' ";
        }
        command += "))";
        
        int[] keyCol = {1};

        WebRowSet wrs =  JDBCManager.getInstance().getRowSet(command, keyCol);
        try
        {
            while(wrs.next())
            {
                Pizza currentPizza = new Pizza(wrs.getInt(1), wrs.getString(2), wrs.getBoolean(3), wrs.getBoolean(4), wrs.getDouble(5));
                pizzaMap.put(wrs.getInt(1), currentPizza);
                pizzaList.add(currentPizza);
            }
        } catch (SQLException ex)
        {
            ex.printStackTrace();
        }                  
    }
    
    
    
    
    //////////////////////
    // Getters and Setters

    public BeanManager getBeanManager()
    {
        return beanManager;
    }

    public void setBeanManager(BeanManager beanManager)
    {
        this.beanManager = beanManager;
    }

    public Map<Integer, Pizza> getPizzaMap()
    {
        return pizzaMap;
    }

    public void setPizzaMap(Map<Integer, Pizza> pizzaMap)
    {
        this.pizzaMap = pizzaMap;
    }

    public List<Pizza> getPizzaList()
    {
        return pizzaList;
    }

    public void setPizzaList(List<Pizza> pizzaList)
    {
        this.pizzaList = pizzaList;
    }

    public Pizza getCurrentPizza()
    {
        return currentPizza;
    }

    public void setCurrentPizza(Pizza currentPizza)
    {
        this.currentPizza = currentPizza;
    }




    
    
}
